﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using KVB.Common;

namespace Customer_Profile.eCard.DBDao
{
    public class DealDao
    {
        public static string AddMMDeal(string if_agentSale,
            string agent_id,
            string cparty_id, 
            string prod_code, 
            string due_prodcode,
            string due_prodremark,
            string card_no, 
            decimal real_amount,
            decimal should_amount,
            decimal stand_discount,
            decimal real_discount,
            decimal incard_amount,
            decimal amount_spread, 
            string value_date, 
            string due_date,
            string entity_id, 
            string entity, 
            string dealer,
            string inputUserid,
            string if_especial,
            string especial_remark,
            string remark,
            string Designee
            )          //添加一个MMDeal        
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@if_agentSale ", SqlDbType.VarChar,50),
                                             new SqlParameter("@agent_id", SqlDbType.VarChar,50),
                                             new SqlParameter("@cparty_id", SqlDbType.VarChar,50),
                                             new SqlParameter("@prod_code",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_prodcode",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_prodremark",SqlDbType.VarChar,250),
                                             new SqlParameter("@card_no",SqlDbType.VarChar,50 ),
                                             new SqlParameter("@real_amount",SqlDbType.Decimal),
                                             new SqlParameter("@should_amount",SqlDbType.Decimal),
                                             new SqlParameter("@stand_discount",SqlDbType.Decimal),                                             
                                             new SqlParameter("@real_discount",SqlDbType.Decimal),
                                             new SqlParameter("@incard_amount",SqlDbType.Decimal),
                                             new SqlParameter("@amount_spread",SqlDbType.Decimal),
                                             new SqlParameter("@value_date",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_date",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity_id",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity",SqlDbType.VarChar,50),
                                             new SqlParameter("@dealer",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@input_userid",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@if_especial",SqlDbType.VarChar,50),
                                             new SqlParameter("@especial_remark",SqlDbType.VarChar,500),
                                             new SqlParameter("@remark",SqlDbType.VarChar,50),
                                             new SqlParameter("@designee",SqlDbType.NVarChar,4000),
                                             new SqlParameter("@deal_no",SqlDbType.VarChar,50),
                                           };

                parameters[23].Direction = ParameterDirection.Output;
                parameters[0].Value = if_agentSale;
                parameters[1].Value = agent_id;
                parameters[2].Value = cparty_id;
                parameters[3].Value = prod_code;
                parameters[4].Value = due_prodcode;
                parameters[5].Value = due_prodremark;
                parameters[6].Value = card_no;
                parameters[7].Value = real_amount;
                parameters[8].Value = should_amount;
                parameters[9].Value = stand_discount;                
                parameters[10].Value = real_discount;
                parameters[11].Value = incard_amount;
                parameters[12].Value = amount_spread;
                parameters[13].Value = value_date;
                parameters[14].Value = due_date;
                parameters[15].Value = entity_id;
                parameters[16].Value = entity;
                parameters[17].Value = dealer;
                parameters[18].Value = inputUserid;
                parameters[19].Value = if_especial;
                parameters[20].Value = especial_remark;
                parameters[21].Value = remark;
                parameters[22].Value = Designee;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_insert", parameters);
                return parameters[23].Value.ToString();
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.AddMMDeal", e.Message);
                return "";
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static string AddMMDeal_Extend(string if_agentSale,
           string agent_id,
           string cparty_id,
           string prod_code,
           string due_prodcode,
           string due_prodremark,
           string card_no,
           decimal real_amount,
           decimal should_amount,
           decimal stand_discount,
           decimal real_discount,
           decimal incard_amount,
           decimal amount_spread,
           string value_date,
           string due_date,
           string entity_id,
           string entity,
           string dealer,
           string inputUserid,
           string remark,
           string designee,
           string extend_all_info)          //添加一个MMDeal                
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@if_agentSale ", SqlDbType.VarChar,50),
                                             new SqlParameter("@agent_id ", SqlDbType.VarChar,50),
                                             new SqlParameter("@cparty_id ", SqlDbType.VarChar,50),
                                             new SqlParameter("@prod_code ",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_prodcode ",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_prodremark ",SqlDbType.VarChar,50),
                                             new SqlParameter("@card_no ",SqlDbType.VarChar,50 ),
                                             new SqlParameter("@real_amount",SqlDbType.Decimal),
                                             new SqlParameter("@should_amount",SqlDbType.Decimal),
                                             new SqlParameter("@stand_discount ",SqlDbType.Decimal),                                             
                                             new SqlParameter("@real_discount ",SqlDbType.Decimal),
                                             new SqlParameter("@incard_amount ",SqlDbType.Decimal),
                                             new SqlParameter("@amount_spread ",SqlDbType.Decimal),
                                             new SqlParameter("@value_date ",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_date ",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity_id",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity",SqlDbType.VarChar,50),
                                             new SqlParameter("@dealer",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@input_userid ",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@remark",SqlDbType.VarChar,50),
                                             new SqlParameter("@designee",SqlDbType.NVarChar,4000) ,  
                                             new SqlParameter("@extend_all_info",SqlDbType.VarChar,500) ,                                               
                                             new SqlParameter("@deal_no",SqlDbType.VarChar,50)                                                                            
                                           };

                parameters[22].Direction = ParameterDirection.Output;
                parameters[0].Value = if_agentSale;
                parameters[1].Value = agent_id;
                parameters[2].Value = cparty_id;
                parameters[3].Value = prod_code;
                parameters[4].Value = due_prodcode;
                parameters[5].Value = due_prodremark;
                parameters[6].Value = card_no;
                parameters[7].Value = real_amount;
                parameters[8].Value = should_amount;
                parameters[9].Value = stand_discount;
                parameters[10].Value = real_discount;
                parameters[11].Value = incard_amount;
                parameters[12].Value = amount_spread;
                parameters[13].Value = value_date;
                parameters[14].Value = due_date;
                parameters[15].Value = entity_id;
                parameters[16].Value = entity;
                parameters[17].Value = dealer;
                parameters[18].Value = inputUserid;
                parameters[19].Value = remark;
                parameters[20].Value = designee;
                parameters[21].Value = extend_all_info;                

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_extend_insert", parameters);
                return parameters[22].Value.ToString();
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.AddMMDeal_Extend", e.Message);
                return "";
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static bool UpdateMMDeal_Extend(int deal_no,
              string if_agentSale,
              string agent_id,
              string cparty_id,
              string prod_code,
              string due_prodcode,
              string due_prodremark,
              string card_no,
              decimal real_amount,
              decimal should_amount,
              decimal stand_discount,
              decimal real_discount,
              decimal incard_amount,
              decimal amount_spread,
              string value_date,
              string due_date,
              string entity_id,
              string entity,
              string dealer,
              string inputUserid,
              string remark,
              string designee,
              string extend_all_info)          //添加一个MMDeal
        {
            bool bok = false;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@if_agentSale ", SqlDbType.VarChar,50),
                                             new SqlParameter("@agent_id ", SqlDbType.VarChar,50),
                                             new SqlParameter("@cparty_id ", SqlDbType.VarChar,50),
                                             new SqlParameter("@prod_code ",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_prodcode ",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_prodremark ",SqlDbType.VarChar,50),
                                             new SqlParameter("@card_no ",SqlDbType.VarChar,50 ),
                                             new SqlParameter("@real_amount",SqlDbType.Decimal),
                                             new SqlParameter("@should_amount",SqlDbType.Decimal),
                                             new SqlParameter("@stand_discount ",SqlDbType.Decimal),                                             
                                             new SqlParameter("@real_discount ",SqlDbType.Decimal),
                                             new SqlParameter("@incard_amount ",SqlDbType.Decimal),
                                             new SqlParameter("@amount_spread ",SqlDbType.Decimal),
                                             new SqlParameter("@value_date ",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_date ",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity_id",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity ",SqlDbType.VarChar,50),
                                             new SqlParameter("@dealer ",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@input_userid ",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@remark ",SqlDbType.NVarChar,500),
                                             new SqlParameter("@designee",SqlDbType.NVarChar,4000),
                                             new SqlParameter("@extend_all_info ",SqlDbType.VarChar,500),
                                             new SqlParameter("@deal_no ",SqlDbType.Int)
                                           };

                parameters[0].Value = if_agentSale;
                parameters[1].Value = agent_id;
                parameters[2].Value = cparty_id;
                parameters[3].Value = prod_code;
                parameters[4].Value = due_prodcode;
                parameters[5].Value = due_prodremark;
                parameters[6].Value = card_no;
                parameters[7].Value = real_amount;
                parameters[8].Value = should_amount;
                parameters[9].Value = stand_discount;
                parameters[10].Value = real_discount;
                parameters[11].Value = incard_amount;
                parameters[12].Value = amount_spread;
                parameters[13].Value = value_date;
                parameters[14].Value = due_date;
                parameters[15].Value = entity_id;
                parameters[16].Value = entity;
                parameters[17].Value = dealer;
                parameters[18].Value = inputUserid;
                parameters[19].Value = remark;
                parameters[20].Value = designee;
                parameters[21].Value = extend_all_info;
                parameters[22].Value = deal_no;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_extend_update", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.UpdateMMDeal_Extend", e.Message);
                bok = false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }            
            return bok;
        }


        public static bool UpdateMMDeal(string deal_no,
            string if_agentSale,
            string agent_id,
            string cparty_id,
            string prod_code,
            string due_prodcode,
            string due_prodremark,
            string card_no,
            decimal real_amount,
            decimal should_amount,
            decimal stand_discount,
            decimal real_discount,
            decimal incard_amount,
            decimal amount_spread,
            string value_date,
            string due_date,
            string entity_id,
            string entity,
            string dealer,
            string inputUserid,
            string if_especial,
            string especial_remark,
            string remark,
            string designee
            )          //修改一个MMDeal            
        {
            SqlConnection conn = null;
            bool bok = false;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={
     
                                            
                                             new SqlParameter("@if_agentSale", SqlDbType.VarChar,50),
                                             new SqlParameter("@agent_id", SqlDbType.VarChar,50),
                                             new SqlParameter("@cparty_id", SqlDbType.VarChar,50),
                                             new SqlParameter("@prod_code",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_prodcode",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_prodremark",SqlDbType.VarChar,250),
                                             new SqlParameter("@card_no",SqlDbType.VarChar,50 ),
                                             new SqlParameter("@real_amount",SqlDbType.Decimal),
                                             new SqlParameter("@should_amount",SqlDbType.Decimal),
                                             new SqlParameter("@stand_discount",SqlDbType.Decimal),
                                             new SqlParameter("@real_discount",SqlDbType.Decimal),
                                             new SqlParameter("@incard_amount",SqlDbType.Decimal),
                                             new SqlParameter("@amount_spread",SqlDbType.Decimal),
                                             new SqlParameter("@value_date",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_date",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity_id",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity",SqlDbType.VarChar,50),
                                             new SqlParameter("@dealer",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@input_userid ",SqlDbType.VarChar,50),
                                             new SqlParameter("@if_especial",SqlDbType.VarChar,50),
                                             new SqlParameter("@especial_remark",SqlDbType.VarChar,500),
                                             new SqlParameter("@remark",SqlDbType.VarChar,50),
                                             new SqlParameter("@deal_no",SqlDbType.VarChar,50),
                                             new SqlParameter("@designee",SqlDbType.NVarChar,4000),
                                           };               
                parameters[0].Value = if_agentSale;
                parameters[1].Value = agent_id;
                parameters[2].Value = cparty_id;
                parameters[3].Value = prod_code;
                parameters[4].Value = due_prodcode;
                parameters[5].Value = due_prodremark;
                parameters[6].Value = card_no;
                parameters[7].Value = real_amount;
                parameters[8].Value = should_amount;
                parameters[9].Value = stand_discount;
                parameters[10].Value = real_discount;
                parameters[11].Value = incard_amount;
                parameters[12].Value = amount_spread;
                parameters[13].Value = value_date;
                parameters[14].Value = due_date;
                parameters[15].Value = entity_id;
                parameters[16].Value = entity;
                parameters[17].Value = dealer;
                parameters[18].Value = inputUserid;
                parameters[19].Value = if_especial;
                parameters[20].Value = especial_remark;
                parameters[21].Value = remark;
                parameters[22].Value = deal_no;
                parameters[23].Value = designee;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_Update", parameters);
                bok = true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.UpdateMMDeal", e.Message);
                bok = false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bok;
        }

        #region
        /*
        public static string AddMMDeal_byAgent(string agent_id,
          string cparty_id,
          string prod_code,
          string card_no,
          decimal real_amount,
          decimal should_amount,
          decimal stand_discount,
          decimal real_discount,
          decimal incard_amount,
          decimal amount_spread,
          string value_date,
          string due_date,
          string entity,
          string dealer,
          string inputUserid,
          string remark)          //添加一个MMDeal
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@cparty_id ", SqlDbType.VarChar,50),
                                             new SqlParameter("@prod_code ",SqlDbType.VarChar,50),
                                             new SqlParameter("@card_no ",SqlDbType.VarChar,50 ),
                                             new SqlParameter("@real_amount",SqlDbType.Decimal),
                                             new SqlParameter("@should_amount",SqlDbType.Decimal),
                                             new SqlParameter("@stand_discount ",SqlDbType.Decimal),
                                             new SqlParameter("@real_discount ",SqlDbType.Decimal),
                                             new SqlParameter("@incard_amount ",SqlDbType.Decimal),
                                             new SqlParameter("@amount_spread ",SqlDbType.Decimal),
                                             new SqlParameter("@value_date ",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_date ",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity ",SqlDbType.VarChar,50),
                                             new SqlParameter("@dealer ",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@input_userid ",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@remark ",SqlDbType.VarChar,50),
                                             new SqlParameter("@agent_id ",SqlDbType.VarChar,50),
                                             new SqlParameter("@deal_no ",SqlDbType.VarChar,50),
                                           };

                //@real_amount decimal(18,2),
                // @should_amount decimal(18,2),
                // @stand_discount decimal(18,2),
                // @real_discount decimal(18,2),
                // @incard_amount decimal(18,2),
                // @amount_spread	decimal(18,2),

                parameters[16].Direction = ParameterDirection.Output;
                parameters[0].Value = cparty_id;
                parameters[1].Value = prod_code;
                parameters[2].Value = card_no;
                parameters[3].Value = real_amount;
                parameters[4].Value = should_amount;
                parameters[5].Value = stand_discount;
                parameters[6].Value = real_discount;
                parameters[7].Value = incard_amount;
                parameters[8].Value = amount_spread;
                parameters[9].Value = value_date;
                parameters[10].Value = due_date;
                parameters[11].Value = entity;
                parameters[12].Value = dealer;
                parameters[13].Value = inputUserid;
                parameters[14].Value = remark;
                parameters[15].Value = agent_id;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_insert_byagent", parameters);
                return parameters[16].Value.ToString();
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.AddMMDeal", e.Message);
                return "";
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }
        */
        /*
        public static string UpdateMMDeal_byAgent(string agent_id,
          string cparty_id,
          string prod_code,
          string card_no,
          decimal real_amount,
          decimal should_amount,
          decimal stand_discount,
          decimal real_discount,
          decimal incard_amount,
          decimal amount_spread,
          string value_date,
          string due_date,
          string entity,
          string dealer,
          string inputUserid,
          string remark,
          string dealno)          //修改一个MMDeal
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@cparty_id ", SqlDbType.VarChar,50),
                                             new SqlParameter("@prod_code ",SqlDbType.VarChar,50),
                                             new SqlParameter("@card_no ",SqlDbType.VarChar,50 ),
                                             new SqlParameter("@real_amount",SqlDbType.Decimal),
                                             new SqlParameter("@should_amount",SqlDbType.Decimal),
                                             new SqlParameter("@stand_discount ",SqlDbType.Decimal),
                                             new SqlParameter("@real_discount ",SqlDbType.Decimal),
                                             new SqlParameter("@incard_amount ",SqlDbType.Decimal),
                                             new SqlParameter("@amount_spread ",SqlDbType.Decimal),
                                             new SqlParameter("@value_date ",SqlDbType.VarChar,50),
                                             new SqlParameter("@due_date ",SqlDbType.VarChar,50),
                                             new SqlParameter("@entity ",SqlDbType.VarChar,50),
                                             new SqlParameter("@dealer ",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@input_userid ",SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@remark ",SqlDbType.VarChar,50),
                                             new SqlParameter("@agent_id ",SqlDbType.VarChar,50),
                                             new SqlParameter("@deal_no ",SqlDbType.VarChar,50),
                                          };
                parameters[0].Value = cparty_id;
                parameters[1].Value = prod_code;
                parameters[2].Value = card_no;
                parameters[3].Value = real_amount;
                parameters[4].Value = should_amount;
                parameters[5].Value = stand_discount;
                parameters[6].Value = real_discount;
                parameters[7].Value = incard_amount;
                parameters[8].Value = amount_spread;
                parameters[9].Value = value_date;
                parameters[10].Value = due_date;
                parameters[11].Value = entity;
                parameters[12].Value = dealer;
                parameters[13].Value = inputUserid;
                parameters[14].Value = remark;
                parameters[15].Value = agent_id;
                parameters[16].Value = dealno;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_update_byagent", parameters);
                return parameters[16].Value.ToString();
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.updateMMDeal", e.Message);
                return "";
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }
        */
        #endregion

        public static bool SubmitMMDeal(int deal_no,string status)          //提交交易到后台
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@deal_no ", SqlDbType.Int),
                                             new SqlParameter("@status ", SqlDbType.VarChar,50)                                             
                                           };

                parameters[0].Value = deal_no;
                parameters[1].Value = status;                

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_submit", parameters);                
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.SubmitMMDeal", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static bool CancelMMDeal(int deal_no)          //提交交易到后台, string status
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@deal_no ", SqlDbType.Int)
                                             //new SqlParameter("@status ", SqlDbType.VarChar,50)
                                           };

                parameters[0].Value = deal_no;
                //parameters[1].Value = status;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_cancel", parameters);
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.CancelMMDeal", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static bool CancelMMDeal_Extend(int deal_no)          //提交交易到后台
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@deal_no ", SqlDbType.Int)                                             
                                           };

                parameters[0].Value = deal_no;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_cancelExtend", parameters);
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.CancelMMDeal", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet getCardDealsbysome(string strWhere)          //搜索Deals
        {
            string strSQL = "SELECT d.deal_no,card_no,c.cparty_name,p.procduct_name,(SELECT procduct_name FROM dbo.prod_type WHERE procduct_code = d.due_prodcode ) AS due_procduct_name,d.submit_date,d.review_date,d.value_date,d.due_date,tc.CodeName dealstatus,status,real_discount, initial_amount,due_amount,deal_balance,d.entity,d.remark,if_isExtension,d.if_agentsale,d.if_returncard,d.input_userid ";
            //strSQL += " ss.settle_amount,ss.settle_fee,ss.pay_amount ";
            strSQL += "  FROM dbo.mmdeals d ";
            strSQL += "  INNER JOIN dbo.cparty c on d.cparty_id = c.cparty_id  ";
            strSQL += "  inner join prod_type p on d.prod_code = p.procduct_code ";
            strSQL += "  INNER JOIN dbo.TP_TCode tc ON tc.CodeValue =d.status AND tc.TypeID =(SELECT ID FROM dbo.TP_TType WHERE CodeTypeName ='dealStatus') ";
            strSQL += strWhere;
            //Log.WriteDefaultLog("SQL:", strSQL);
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getCardDealsbysome", e.Message + "SQL:" + strSQL);
                throw e;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getCardSettleDeals(string strWhere)          //搜索Deals
        {
            string strSQL = "SELECT ss.settle_id,ss.settle_amount,ss.settle_commission_percent,ss.settle_fee ,ss.input_userid,ss.pay_amount,d.deal_no,card_no,c.cparty_name,p.procduct_name,d.value_date,d.due_date,ss.submit_date settle_submit_date,ss.settle_status, tc.CodeName settle_status_name,status,real_discount, initial_amount,due_amount,d.entity,d.remark,if_isExtension ";
            //strSQL += " ss.settle_amount,ss.settle_fee,ss.pay_amount ";
            strSQL += "  from dbo.settle_subinfo ss ";
            strSQL += "  INNER JOIN dbo.mmdeals d ON ss.deal_no =d.deal_no ";
            strSQL += "  INNER JOIN dbo.cparty c on d.cparty_id = c.cparty_id  ";
            strSQL += "  inner join prod_type p on d.prod_code = p.procduct_code ";
            strSQL += "  INNER JOIN dbo.TP_TCode tc ON tc.CodeValue =ss.settle_status AND tc.TypeID =(SELECT ID FROM dbo.TP_TType WHERE CodeTypeName ='SettleStatus') ";
            strSQL += strWhere;            
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getCardDealsneedSettle", e.Message+"SQL:"+strSQL);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getCardDealsneedSettle(string strWhere)          //搜索Deals
        {
            string strSQL = "SELECT ss.settle_id,ss.settle_amount,ss.settle_fee ,ss.pay_amount,ss.remark as settle_remark,(select settle_agent_name from settle_agent where settle_agent_id =ss.settle_agent_id) as settle_agent_name,d.deal_no,card_no,c.cparty_name,p.procduct_name,d.value_date,d.due_date,tc.CodeName dealstatus,status,real_discount, initial_amount,due_amount,d.entity,d.remark,if_isExtension ";
            //strSQL += " ss.settle_amount,ss.settle_fee,ss.pay_amount ";
            strSQL += "  from dbo.settle_subinfo ss ";
            strSQL += "  INNER JOIN dbo.mmdeals d ON ss.deal_no =d.deal_no ";
            strSQL += "  INNER JOIN dbo.cparty c on d.cparty_id = c.cparty_id  ";
            //strSQL += "  INNER JOIN dbo.settle_agent sa on sa.settle_agent_id = ss.settle_agent_id  ";
            strSQL += "  inner join prod_type p on d.prod_code = p.procduct_code ";
            strSQL += "  INNER JOIN dbo.TP_TCode tc ON tc.CodeValue =d.status AND tc.TypeID =(SELECT ID FROM dbo.TP_TType WHERE CodeTypeName ='dealStatus') ";            
            strSQL += strWhere;

            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getCardDealsneedSettle", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getNeedMatch(string strWhere)          //搜索Deals
        {
            string strSQL = "select d.deal_no,card_no,c.cparty_name,p.procduct_name,d.real_discount,d.initial_amount,d.due_amount,";
            strSQL += "d.value_date,d.due_date,d.entity,d.submit_date,tc.CodeName dealstatus,d.status,d.remark,d.if_isExtension from mmdeals d inner join cparty c on d.cparty_id = c.cparty_id inner join prod_type p on d.prod_code = p.procduct_code INNER JOIN dbo.TP_TCode tc ON tc.CodeValue =d.status AND tc.TypeID =(SELECT ID FROM dbo.TP_TType WHERE CodeTypeName ='dealStatus')";
            strSQL += strWhere;
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getNeedMatch", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getExtendInfoByNewDealno(int dealno)          //搜索Deals
        {
            string strSQL = "SELECT d.deal_no,d.card_no,c.cparty_name,p.procduct_name,(SELECT procduct_name FROM dbo.prod_type WHERE procduct_code = d.due_prodcode ) AS due_procduct_name,d.due_prodremark,d.real_discount,d.initial_amount,d.due_amount, d.deal_balance as balance_amount,d.value_date,d.due_date ,e.extend_amount,e.old_deal_balance,d.if_agentsale,(SELECT sale_agent_name FROM dbo.sale_agent WHERE sale_agent_id =d.agent_id) AS sale_agent_name  FROM dbo.extend_detail e ";
            //string strSQL = "select d.deal_no,card_no,c.cparty_name,p.procduct_name,(SELECT procduct_name FROM dbo.prod_type WHERE procduct_code = d.due_prodcode ) AS due_procduct_name,d.due_prodremark,d.real_discount,d.initial_amount,d.due_amount, d.deal_balance as balance_amount,d.value_date,d.due_date,deal_balance as extend_amount,'0' as old_deal_balance,d.if_agentsale,(SELECT sale_agent_name FROM dbo.sale_agent WHERE sale_agent_id =d.agent_id) AS sale_agent_name ";
            strSQL += "INNER JOIN dbo.mmdeals d ON e.old_deal_no = d.deal_no  inner join cparty c on d.cparty_id = c.cparty_id inner join prod_type p on d.prod_code = p.procduct_code ";
                   strSQL+= " WHERE new_deal_no=" + dealno;
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getExtendInfoByNewCardno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static bool reNew(string deal_no,
                string prod_code,
                string deal_term,
                string card_no,
                decimal real_count,
                decimal real_discount,
                decimal incard_count,
                DateTime value_date,
                DateTime due_date,
                string remark,
                string if_again,
                string again_period,
                string if_agent,
                string agent_name
            )          //添加一个MMDeal
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@deal_no", SqlDbType.VarChar,50),
                                             new SqlParameter("@prod_code",SqlDbType.VarChar,50),
                                             new SqlParameter("@deal_term",SqlDbType.VarChar,50),
                                             new SqlParameter("@card_no",SqlDbType.VarChar,50 ),
                                             new SqlParameter("@real_count",SqlDbType.Decimal),
                                             new SqlParameter("@real_discount",SqlDbType.Decimal),
                                             new SqlParameter("@incard_count",SqlDbType.Decimal),
                                             new SqlParameter("@value_date",SqlDbType.DateTime),
                                             new SqlParameter("@due_date",SqlDbType.DateTime),
                                             new SqlParameter("@remark",SqlDbType.NVarChar,250),
                                             new SqlParameter("@ifneed_again",SqlDbType.VarChar,50),
                                             new SqlParameter("@again_period",SqlDbType.NVarChar,250),
                                             new SqlParameter("@if_agent",SqlDbType.VarChar,50),
                                             new SqlParameter("@agent_name",SqlDbType.NVarChar,250),
                                           };

                parameters[0].Value = deal_no;
                parameters[1].Value = prod_code;
                parameters[2].Value = deal_term;
                parameters[3].Value = card_no;
                parameters[4].Value = real_count;
                parameters[5].Value = real_discount;
                parameters[6].Value = incard_count;
                parameters[7].Value = value_date;
                parameters[8].Value = due_date;
                parameters[9].Value = remark;
                parameters[10].Value = if_again;
                parameters[11].Value = again_period;
                parameters[12].Value = if_agent;
                parameters[13].Value = agent_name;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_reNew", parameters);
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.reNew", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        //审核通过结算申请
        public static bool settle_check_pass(string settle_id,string user_id,string pass_remark)
        {     
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@settle_id", SqlDbType.Int),
                                               new SqlParameter("@review_userid", SqlDbType.VarChar,50),
                                             new SqlParameter("@pass_remark",SqlDbType.VarChar,250)                                             
                                           };

                parameters[0].Value = settle_id;
                parameters[1].Value = user_id;
                parameters[2].Value = pass_remark;                 

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_pass", parameters);
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.settle_check_pass", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        //审核通过结算申请
        public static bool check_ifRepeat_requestSettle(string settle_id)
        {
            bool bOK = false;
            SqlConnection conn = null;
            string strSQL = "SELECT COUNT(1) FROM settle_subinfo WHERE deal_no =(SELECT deal_no FROM dbo.settle_subinfo WHERE settle_id= " + settle_id + ") AND dbo.settle_subinfo.settle_status!='PASS'";
            try
            {
                DB.Connection(ref conn);
                DataSet ds=DB.ExecuteDataset(conn, CommandType.Text, strSQL);
                if (int.Parse(ds.Tables[0].Rows[0][0].ToString()) > 1)
                    return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.settle_check_pass", e.Message);
                return true;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return bOK;
        }

        //审核退回结算申请
        public static bool settle_check_back(string settle_id, string user_id, string back_reason)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@settle_id", SqlDbType.Int),
                                               new SqlParameter("@review_userid", SqlDbType.VarChar,50),
                                             new SqlParameter("@back_reason",SqlDbType.VarChar,250)                                             
                                           };

                parameters[0].Value = settle_id;
                parameters[1].Value = user_id;
                parameters[2].Value = back_reason;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_back", parameters);
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.settle_check_back", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet getDeals()          //搜索Deals
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_deals_bysome");
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getDeals", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }
        //eCard_deals_deatil
        public static DataSet getDealsByCardno(string card_no)          //搜索Deals
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@card_no ", SqlDbType.VarChar, 50) };
                parameters[0].Value = card_no;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_deals_deatil",parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getDealsByCardno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getCardBalance(string card_no,out string status,out string message)          //搜索Deals
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@card_no ", SqlDbType.VarChar, 50) ,
                                                new SqlParameter("@status", SqlDbType.VarChar, 50) ,
                                                new SqlParameter("@message", SqlDbType.VarChar, 250) 
                                            };
                parameters[0].Value = card_no;
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_card_getbalance", parameters);
                status = parameters[1].Value.ToString();
                message = parameters[2].Value.ToString();
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getCardBalance", e.Message);
                status = "N";
                message = e.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet CustomergetCardBalance(string card_no, string id_no,out string status, out string message)          //搜索Deals
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { 
                                                new SqlParameter("@card_no ", SqlDbType.VarChar, 50) ,
                                                new SqlParameter("@id_no ", SqlDbType.VarChar, 50) ,
                                                new SqlParameter("@status", SqlDbType.VarChar, 50) ,
                                                new SqlParameter("@message", SqlDbType.VarChar, 250) 
                                            };
                parameters[0].Value = card_no;
                parameters[1].Value = id_no;
                parameters[2].Direction = ParameterDirection.Output;
                parameters[3].Direction = ParameterDirection.Output;

                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_card_Customergetbalance", parameters);
                status = parameters[2].Value.ToString();
                message = parameters[3].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.CustomergetCardBalance", ex.Message);
                status = "N";
                message = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getMMDeals()          //搜索Deals
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_MMdeals_bysome");
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getMMDeals", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static bool Refund(string dealno,string refund_date)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                               new SqlParameter("@deal_no", SqlDbType.VarChar,250),
                                               new SqlParameter("@refund_date", SqlDbType.VarChar,250)
                                           };

                parameters[0].Value = dealno;
                parameters[1].Value = refund_date;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_refund", parameters);
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.Refund", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static bool Valid(string dealno)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@deal_no", SqlDbType.VarChar, 50) };

                parameters[0].Value = dealno;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_valid", parameters);
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.Valid", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet getMMDealsbyDealno(string deal_no)          //搜索Deals
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ new SqlParameter("@deal_no ", SqlDbType.VarChar,50)};
                parameters[0].Value = deal_no;
                
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_MMdeals_bydealno",parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getMMDealsbyDealno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static bool settle(string dealno,string settleAgent,string settleInputDate,string payDate)
        {
            SqlConnection conn = null;
            try
            {                               //@settle_agent_name
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@deal_no", SqlDbType.VarChar, 50) ,
                                                new SqlParameter("@settle_agent_name", SqlDbType.NVarChar, 250),
                                                new SqlParameter("@settle_input_date", SqlDbType.VarChar, 50), 
                                                new SqlParameter("@settle_date", SqlDbType.VarChar, 50) 
                                            };

                parameters[0].Value = dealno;
                parameters[1].Value = settleAgent;
                parameters[2].Value = settleInputDate;
                parameters[3].Value = payDate;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_mmdeal_settle", parameters);
                return true;
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.settle", e.Message);
                return false;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet EntitySearchDealbyDealno(string dealno)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@deal_no", SqlDbType.VarChar, 50) };
                parameters[0].Value = dealno;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_getDeals_bydealno", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.EntitySearchDealbyCardno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getSettleInfobySettleID(string ref_settle_id)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = { new SqlParameter("@settle_id", SqlDbType.Int) };
                parameters[0].Value = int.Parse(ref_settle_id);
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_SettleInfo_bySettleID", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.EntitySearchDealbyCardno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getIfLocked(string ref_settle_id)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                //SqlParameter[] parameters = { new SqlParameter("@settle_id", SqlDbType.Int) };
                //parameters[0].Value = int.Parse(ref_settle_id);
                string strSQL = "SELECT if_locked FROM dbo.mmdeals WHERE deal_no =(SELECT deal_no FROM dbo.settle_subinfo WHERE settle_id=" + ref_settle_id + ")  ";
                ds = DB.ExecuteDataset(conn, CommandType.Text,strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getIfLocked", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getIfLocked_byExpenseno(string ref_expense_no)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                //SqlParameter[] parameters = { new SqlParameter("@settle_id", SqlDbType.Int) };
                //parameters[0].Value = int.Parse(ref_settle_id);
                string strSQL = "SELECT if_locked FROM dbo.mmdeals WHERE deal_no =(SELECT deal_no FROM expense_deals WHERE expense_no=" + ref_expense_no + ")  ";
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getIfLocked", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getIfLocked_bydealno(string deal_no)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                string strSQL = "SELECT if_locked FROM dbo.mmdeals WHERE deal_no ="+deal_no;
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getIfLocked_bydealno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static void LockRefDeals(string ref_settle_id)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                string strSQL = "UPDATE  dbo.mmdeals SET if_locked='Y' WHERE deal_no =(SELECT deal_no FROM dbo.settle_subinfo WHERE settle_id=" + ref_settle_id + ")  ";
                DB.ExecuteNonQuery(conn, CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.LockRefDeals", ex.Message);
                throw ex;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void LockRefDeals_byDealno(string ref_Deal_no)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                string strSQL = "UPDATE  dbo.mmdeals SET if_locked='Y' WHERE deal_no =" + ref_Deal_no.Trim() + ")  ";
                DB.ExecuteNonQuery(conn, CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.LockRefDeals_byDealno", ex.Message);
                throw ex;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet getDealBalance_bySettleid(string ref_settle_id)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                string strSQL = "SELECT deal_balance FROM dbo.mmdeals WHERE deal_no =(SELECT deal_no FROM dbo.settle_subinfo WHERE settle_id=" + ref_settle_id + ") ";
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getDealBalance_bySettleid", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static void DealssettleInsert(int dealno, decimal settle_amount, decimal settle_commission_percent, decimal settle_fee,
            decimal pay_amount,string settle_agent_id,
            string account_name,string account_no,string bank_name,string bank_branch,string bank_sub_branch,string bankaccount_location,string bankaccount_city,
            string input_userid, string remark,out string status ,out string message)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                               
                                                new SqlParameter("@deal_no", SqlDbType.Int),                                                
                                                new SqlParameter("@settle_amount", SqlDbType.Decimal),                                                   
                                                new SqlParameter("@settle_commission_percent", SqlDbType.Decimal),   
                                                new SqlParameter("@settle_fee", SqlDbType.Decimal),
                                                new SqlParameter("@pay_amount", SqlDbType.Decimal),
                                                new SqlParameter("@settle_agent_id", SqlDbType.VarChar,50),
                                                new SqlParameter("@remark", SqlDbType.VarChar,250),                                                
                                                new SqlParameter("@input_userid", SqlDbType.VarChar,50),  
                                              
                                                
                                                new SqlParameter("@account_name", SqlDbType.VarChar,50),  
                                                new SqlParameter("@account_no", SqlDbType.VarChar,50),  
                                                new SqlParameter("@bank_name", SqlDbType.VarChar,50),  
                                                new SqlParameter("@bank_branch", SqlDbType.VarChar,50),  
                                                new SqlParameter("@bank_sub_branch", SqlDbType.VarChar,50),  
                                                new SqlParameter("@location", SqlDbType.VarChar,50),  
                                                new SqlParameter("@city", SqlDbType.VarChar,50),  
                                                


                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = dealno;                
                parameters[1].Value = settle_amount;
                parameters[2].Value = settle_commission_percent;     
                parameters[3].Value = settle_fee;
                parameters[4].Value = pay_amount;
                parameters[5].Value = settle_agent_id;

                parameters[6].Value = remark;
                
                parameters[7].Value = input_userid;
                
                parameters[8].Value = account_name;
                parameters[9].Value = account_no;
                parameters[10].Value = bank_name;
                parameters[11].Value = bank_branch;
                parameters[12].Value = bank_sub_branch;
                parameters[13].Value = bankaccount_location;
                parameters[14].Value = bankaccount_city;

                parameters[15].Direction = ParameterDirection.Output;
                parameters[16].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_insert", parameters);
                status = parameters[15].Value.ToString();
                message = parameters[16].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.DealssettleInsert", ex.Message);
                throw ex;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void DealssettleUpdate(int settle_id, decimal settle_amount, decimal settle_commission_percent, decimal settle_fee, decimal pay_amount, string settle_agent_id,
            string account_name, string account_no, string bank_name, string bank_branch, string bank_sub_branch, string bankaccount_location, string bankaccount_city,
            string remark, 
            
            out string status, out string message)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {
                                                new SqlParameter("@settle_id", SqlDbType.Int),
                                                new SqlParameter("@settle_amount", SqlDbType.Decimal),
                                                new SqlParameter("@settle_commission_percent", SqlDbType.Decimal),
                                                new SqlParameter("@settle_fee", SqlDbType.Decimal),
                                                new SqlParameter("@pay_amount", SqlDbType.Decimal),
                                                new SqlParameter("@settle_agent_id", SqlDbType.VarChar,50),
                                                new SqlParameter("@remark", SqlDbType.VarChar,250),
                                                

                                                 new SqlParameter("@account_name", SqlDbType.VarChar,50),  
                                                new SqlParameter("@account_no", SqlDbType.VarChar,50),  
                                                new SqlParameter("@bank_name", SqlDbType.VarChar,50),  
                                                new SqlParameter("@bank_branch", SqlDbType.VarChar,50),  
                                                new SqlParameter("@bank_sub_branch", SqlDbType.VarChar,50),  
                                                new SqlParameter("@location", SqlDbType.VarChar,50),  
                                                new SqlParameter("@city", SqlDbType.VarChar,50),  


                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = settle_id;
                parameters[1].Value = settle_amount;
                parameters[2].Value = settle_commission_percent;
                parameters[3].Value = settle_fee;
                parameters[4].Value = pay_amount;
                parameters[5].Value = settle_agent_id;
                parameters[6].Value = remark;
                
                parameters[7].Value = account_name;
                parameters[8].Value = account_no;
                parameters[9].Value = bank_name;
                parameters[10].Value = bank_branch;
                parameters[11].Value = bank_sub_branch;
                parameters[12].Value = bankaccount_location;
                parameters[13].Value = bankaccount_city;
                
                parameters[14].Direction = ParameterDirection.Output;
                parameters[15].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_Update", parameters);

                status = parameters[14].Value.ToString();
                message = parameters[15].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.DealssettleUpdate", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void DealssettleCancel(int settle_id, out string status, out string message)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                               
                                                new SqlParameter("@settle_id", SqlDbType.Int),                                                
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = settle_id;                
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_cancel", parameters);
                status = parameters[1].Value.ToString();
                message = parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.DealssettleCancel", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void DealssettleSubmit(int settle_id, out string status, out string message)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                               
                                                new SqlParameter("@settle_id", SqlDbType.Int),                                                
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = settle_id;
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_submit", parameters);
                status = parameters[1].Value.ToString();
                message = parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.DealssettleSubmit", ex.Message);
                throw ex;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void SettleTransfer(string settleidList, 
            string title,
            string input_userid, 
            string input_username,
            decimal dTransferAmount,
            string attach_url,
            string remark, 
            out string status, 
            out string message)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   

                                                new SqlParameter("@settle_id", SqlDbType.VarChar,8000),
                                                new SqlParameter("@title", SqlDbType.VarChar,50),
                                                new SqlParameter("@user_id", SqlDbType.VarChar,50),
                                                new SqlParameter("@user_name", SqlDbType.VarChar,50),
                                                new SqlParameter("@transfer_amount", SqlDbType.Decimal),
                                                new SqlParameter("@attach_url", SqlDbType.VarChar,250),
                                                new SqlParameter("@remark", SqlDbType.VarChar,250),
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = settleidList;
                parameters[1].Value = title;
                parameters[2].Value = input_userid;
                parameters[3].Value = input_username;
                parameters[4].Value = dTransferAmount;
                parameters[5].Value = attach_url;                
                parameters[6].Value = remark;                
                parameters[7].Direction = ParameterDirection.Output;
                parameters[8].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_transferadd", parameters);
                status = parameters[7].Value.ToString();
                message = parameters[8].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.DealssettleInsert", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void BackReView(
            string settleidList,           
            out string status,
            out string message)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {
                                                new SqlParameter("@settle_id", SqlDbType.VarChar,250),                                                
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = settleidList;                
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_BackReView", parameters);
                status = parameters[1].Value.ToString();
                message = parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.BackReView", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet EntitySearchDealbyOldDealno(string dealno)  //根据IDNO获取客户信息
        {
            string strAlldealno = "";
            string[] strList = dealno.Split(',');
            for (int i = 0; i < strList.Length; i++)
            {
                strAlldealno += "'" + strList[i] + "',";
            }
            strAlldealno = strAlldealno.Substring(0, strAlldealno.Length - 1);

            SqlConnection conn = null;
            DataSet ds = null;
            string strSQL = "select d.deal_no,card_no,c.cparty_name,p.procduct_name,(SELECT procduct_name FROM dbo.prod_type WHERE procduct_code = d.due_prodcode ) AS due_procduct_name,d.due_prodremark,d.real_discount,d.initial_amount,d.due_amount, d.deal_balance as balance_amount,d.value_date,d.due_date,deal_balance as extend_amount,'0' as old_deal_balance,d.if_agentsale,(SELECT sale_agent_name FROM dbo.sale_agent WHERE sale_agent_id =d.agent_id) AS sale_agent_name ";
            strSQL += "from mmdeals d inner join cparty c on d.cparty_id = c.cparty_id inner join prod_type p on d.prod_code = p.procduct_code where d.deal_no in (" + strAlldealno + ")";
            try
            {
                DB.Connection(ref conn);
                //SqlParameter[] parameters = { new SqlParameter("@card_no", SqlDbType.VarChar, 50) };
                //parameters[0].Value = cardno;

                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.EntitySearchDealbyOldCardno", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getSettleTransferList(string strStatus)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@status", SqlDbType.VarChar,250)
                                            };
                parameters[0].Value = strStatus;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_settle_transferlist", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.DealssettleInsert", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getConvertList()  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                //SqlParameter[] parameters = {                                                   
                //                                new SqlParameter("@status", SqlDbType.VarChar,250)
                //                            };
                //parameters[0].Value = strStatus;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_settle_Convertlist");
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getConvertList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getConvertListByConvertID(int convert_id)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@convert_id", SqlDbType.Int)
                                            };
                parameters[0].Value = convert_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_netpay_ConvertListbyconvertid",parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getConvertListByConvertID", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }


        public static DataSet getConvertResultByConvertID(int convert_id)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@convert_id", SqlDbType.Int)
                                            };
                parameters[0].Value = convert_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_ConvertResult_List", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getConvertResultByConvertID", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet ConfirmNetPay(string idlist)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@id_list", SqlDbType.VarChar,2500)
                                            };
                parameters[0].Value = idlist;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_ConvertResult_List", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getConvertResultByConvertID", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }


        public static DataSet getSettleSubList(string transfer_id)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@transfer_id", SqlDbType.VarChar,250)
                                            };
                parameters[0].Value = transfer_id;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_settle_sublist", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getSettleSubList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static void confirmPay(int settle_id,string settle_agent_id,string pay_method,decimal dnetbank_fee,decimal dsettle_fee,decimal dpay_amount,out string strStatus ,out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@settle_id", SqlDbType.Int),
                                                new SqlParameter("@settle_agent_id", SqlDbType.VarChar,50),
                                                new SqlParameter("@pay_method", SqlDbType.VarChar,50),
                                                new SqlParameter("@netbank_fee", SqlDbType.Decimal),                                                				
                                                new SqlParameter("@settle_fee", SqlDbType.Decimal),
                                                new SqlParameter("@pay_amount", SqlDbType.Decimal),
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250)
                                            };
                parameters[0].Value = settle_id;
                parameters[1].Value = settle_agent_id;
                parameters[2].Value = pay_method;
                parameters[3].Value = dnetbank_fee;
                parameters[4].Value = dsettle_fee;
                parameters[5].Value = dpay_amount;
                parameters[6].Direction = ParameterDirection.Output;
                parameters[7].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_confirm", parameters);
                strStatus = parameters[6].Value.ToString();
                strMessage = parameters[7].Value.ToString();
            }
            catch (Exception ex)
            {
                strStatus = "N";
                strMessage = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getSettleSubList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void confirmOrder(int settle_id, string settle_agent_id, string pay_method, decimal dNetBankFee, decimal dSettle_fee, decimal dpay_amount, out string strStatus, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@settle_id", SqlDbType.Int),
                                                new SqlParameter("@settle_agent_id", SqlDbType.VarChar,50),
                                                new SqlParameter("@pay_method", SqlDbType.VarChar,50),
                                               
                                                new SqlParameter("@netbank_fee", SqlDbType.Float),
                                                new SqlParameter("@settle_fee", SqlDbType.Float),
                                                new SqlParameter("@pay_amount", SqlDbType.Float),

                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250)
                                            };
                parameters[0].Value = settle_id;
                parameters[1].Value = settle_agent_id;
                parameters[2].Value = pay_method;

                parameters[3].Value = dNetBankFee;
                parameters[4].Value = dSettle_fee;
                parameters[5].Value = dpay_amount;

                parameters[6].Direction = ParameterDirection.Output;
                parameters[7].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_settle_payOrder", parameters);
                strStatus = parameters[6].Value.ToString();
                strMessage = parameters[7].Value.ToString();
            }
            catch (Exception ex)
            {
                strStatus = "N";
                strMessage = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.confirmOrder", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet getSettleDealsList(string strSettleidLIst)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@settle_idlist", SqlDbType.VarChar,8000)
                                            };
                parameters[0].Value = strSettleidLIst;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_settle_transferoldDeals", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.DealssettleInsert", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getConvertList(decimal iStartAmount,decimal iEndAmount, string start_due_date,string end_due_date)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@start_amount", SqlDbType.Float),
                                                new SqlParameter("@end_amount", SqlDbType.Float),
                                                new SqlParameter("@start_due_date", SqlDbType.VarChar,50),
                                                new SqlParameter("@end_due_date", SqlDbType.VarChar,50)
                                            };
                parameters[0].Value = iStartAmount;
                parameters[1].Value = iEndAmount;
                parameters[2].Value = start_due_date;
                parameters[3].Value = end_due_date;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_netpay_ConvertList", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getConvertList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getConvertList_bysettleidlist(string settleidlist)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   
                                                new SqlParameter("@settle_id_list", SqlDbType.VarChar,8000)
                                            };
                parameters[0].Value = settleidlist;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_convert_List", parameters);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.getConvertList_bysettleidlist", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static void cardChange(
            string oldcard_no,
            string newcard_no,
            string input_userid,
            string input_username,            
            string attach_url,
            string remark,
            out string status,
            out string message)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   

                                                new SqlParameter("@oldcard_no", SqlDbType.VarChar,250),
                                                new SqlParameter("@newcard_no", SqlDbType.VarChar,50),
                                                new SqlParameter("@user_id", SqlDbType.VarChar,50),
                                                new SqlParameter("@user_name", SqlDbType.VarChar,50),                                                
                                                new SqlParameter("@attach_url", SqlDbType.VarChar,250),
                                                new SqlParameter("@remark", SqlDbType.VarChar,250),
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = oldcard_no;
                parameters[1].Value = newcard_no;
                parameters[2].Value = input_userid;
                parameters[3].Value = input_username;
                parameters[4].Value = attach_url;
                parameters[5].Value = remark;
                parameters[6].Direction = ParameterDirection.Output;
                parameters[7].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_card_change", parameters);
                status = parameters[6].Value.ToString();
                message = parameters[7].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.cardChange", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void cardReturn(
            string card_no,
            string input_userid,
            string input_username,
            string attach_url,
            string remark,
            out string status,
            out string message)  //根据IDNO获取客户信息
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {                                                   

                                                new SqlParameter("@card_no", SqlDbType.VarChar,250),                                                
                                                new SqlParameter("@user_id", SqlDbType.VarChar,50),
                                                new SqlParameter("@user_name", SqlDbType.VarChar,50),                                                
                                                new SqlParameter("@attach_url", SqlDbType.VarChar,250),
                                                new SqlParameter("@remark", SqlDbType.VarChar,250),
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = card_no;
                parameters[1].Value = input_userid;
                parameters[2].Value = input_username;
                parameters[3].Value = attach_url;
                parameters[4].Value = remark;
                parameters[5].Direction = ParameterDirection.Output;
                parameters[6].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_card_return", parameters);
                status = parameters[5].Value.ToString();
                message = parameters[6].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.cardChange", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void checkCardSatus(string card_no, out string status, out string message)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {
                                                new SqlParameter("@card_no", SqlDbType.VarChar,250),                                                
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = card_no;
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_deals_InsertCheckCardno", parameters);
                status = parameters[1].Value.ToString();
                message = parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.checkCardSatus", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void ExtendcheckCardSatus(string card_no,string extend_all_date, out string status, out string message)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {
                                                new SqlParameter("@card_no", SqlDbType.VarChar,250),
                                                new SqlParameter("@extend_all_info", SqlDbType.VarChar,500),
                                                new SqlParameter("@status", SqlDbType.VarChar,50),
                                                new SqlParameter("@message", SqlDbType.VarChar,250),
                                            };
                parameters[0].Value = card_no;
                parameters[1].Value = extend_all_date;
                parameters[2].Direction = ParameterDirection.Output;
                parameters[3].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_deals_ExTendCheckCardno", parameters);
                status = parameters[2].Value.ToString();
                message = parameters[3].Value.ToString();
            }
            catch (Exception ex)
            {
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.checkCardSatus", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static bool checkiforiginalExtend(int deal_no,out string card_no,out string status,out string message)
        {
            bool if_originalextend=false;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters = {
                                                new SqlParameter("@deal_no", SqlDbType.Int),                                                
                                                new SqlParameter("@card_no", SqlDbType.VarChar,250),
                                                new SqlParameter("@status", SqlDbType.VarChar,250),
                                                new SqlParameter("@message", SqlDbType.VarChar,250)
                                            };
                parameters[0].Value = deal_no;
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;
                parameters[3].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_deals_iforiginalextend", parameters);
                card_no = parameters[1].Value.ToString();
                status = parameters[2].Value.ToString();
                message = parameters[3].Value.ToString();
                if (status.Equals("Y"))
                {
                    if_originalextend = true;
                }
                else
                {
                    if_originalextend = true;
                }
            }
            catch (Exception ex)
            {
                if_originalextend = false;
                card_no = "";
                status = "N";
                message = ex.ToString();
                KVB.Common.Log.WriteDefaultLog("DealDaoDao.checkiforiginalExtend", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return if_originalextend;
        }

        public static DataSet SettleTransferList(string strTitle, string settle_status, string start_date, string end_date)
        {

            string strSQL = "SELECT title,settletransfer_id,input_date,transfer_amount,tc.CodeName settleTransfer_status,attach_url,CASE WHEN attach_url ='' THEN '' ELSE '查看' END AS attach_remark,st.remark FROM dbo.settle_transfer st ";
            strSQL += "       INNER JOIN dbo.TP_TCode tc ";
            strSQL += "ON tc.CodeValue =st.status AND tc.TypeID =(SELECT ID FROM dbo.TP_TType WHERE CodeTypeName ='SettleTransferStatus') where 1=1";
            
            if(!strTitle.Equals(""))
            {
                strSQL += " and st.title like '%" + strTitle +"%' ";
            }
            if (!settle_status.Equals(""))
            {
                strSQL += " and st.status = '" + settle_status + "' ";
            }
            if (!start_date.Equals("") && !end_date.Equals(""))
            {
                strSQL += " and CONVERT(DATE,st.input_date) BETWEEN '"+ start_date +"' AND '" + end_date + "' ";
            }

            strSQL += " order by input_date desc ";

            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);

                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.SettleTransferList", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }


        public static DataSet SettleOrderDetail(string start_date, string end_date)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@start_date", SqlDbType.VarChar,50),
                                             new SqlParameter("@end_date", SqlDbType.VarChar,50)
                                           };
                parameters[0].Value = start_date;
                parameters[1].Value = end_date;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Settle_PayOrderDetal", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("RewardDao.RewardDetailByALL", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }
    }
}